﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Lib_general
{
    public class Struct_Database
    {
        private Exec_Database exc = new Exec_Database();

        public void createschema()
        {
            exc.Exec_data("create table dmchucvu(macv varchar(20),ten nvarchar(200),sudung numeric(1, 0) default 1,userid numeric(5, 0) default 0,ngayud datetime default getdate(),CONSTRAINT pk_dmchucvu PRIMARY KEY(macv))");
            exc.Exec_data("create table dmdvt(madvt varchar(20),ten nvarchar(200),sudung numeric(1, 0) default 1,userid numeric(5, 0) default 0,ngayud datetime default getdate(),CONSTRAINT pk_dmdvt PRIMARY KEY(madvt))");
            exc.Exec_data("create table dmkho(makho varchar(20),ten nvarchar(200),sudung numeric(1, 0) default 1,userid numeric(5, 0) default 0,ngayud datetime default getdate(),CONSTRAINT pk_dmkho PRIMARY KEY(makho))");
            exc.Exec_data("create table dmncc(mancc varchar(20), ten nvarchar(200),diachi nvarchar(200),nguoidaidien nvarchar(50),mst numeric(20, 0),sdt numeric(20, 0),sudung numeric(1, 0) default 1,userid numeric(5, 0) default 0,ngayud datetime default getdate(),CONSTRAINT pk_dmncc PRIMARY KEY(mancc))");
            exc.Exec_data("create table dmsanpham(masp varchar(20),ten nvarchar(200),madvt varchar(20),binhongia numeric(1, 0),sudung numeric(1, 0) default 1,userid numeric(5, 0) default 0,ngayud datetime default getdate(),CONSTRAINT pk_dmsanpham PRIMARY KEY(masp),CONSTRAINT fk_dmsanpham_dmdvt FOREIGN KEY (madvt) REFERENCES dmdvt(madvt))");
            exc.Exec_data("create table dmnhanvien(manv varchar(20),ten nvarchar(200),macv varchar(20),diachi nvarchar(200),sdt numeric(20, 0),mst numeric(20, 0),sudung numeric(1, 0) default 1,userid numeric(5, 0) default 0,ngayud datetime default getdate(),CONSTRAINT pk_dmnhanvien PRIMARY KEY(manv),CONSTRAINT fk_dmnhanvien_dmchucvu FOREIGN KEY (macv) REFERENCES dmchucvu(macv))");
            exc.Exec_data("create table dmdieuchinhgia(madc varchar(20),ten nvarchar(200),masp varchar(20),soluong numeric(20, 2),dongia numeric(19, 4),manv varchar(20),giatruoc numeric(19, 4),giasau numeric(19, 4),ngaythaydoi datetime,sudung numeric(1, 0) default 1,userid numeric(5, 0) default 0,ngayud datetime default getdate(),CONSTRAINT pk_dmdieuchinhgia PRIMARY KEY(madc),CONSTRAINT fk_dmdieuchinhgia_dmnhanvien FOREIGN KEY (manv) REFERENCES dmnhanvien(manv),CONSTRAINT fk_dmdieuchinhgia_dmsanpham FOREIGN KEY (masp) REFERENCES dmsanpham(masp))");
            exc.Exec_data("create table dmquydoi(maqd varchar(20),ten nvarchar(200),masp varchar(20),soluong numeric(20, 2),dongia numeric(19, 4),sudung numeric(1, 0) default 1,userid numeric(5, 0) default 0,ngayud datetime default getdate(),CONSTRAINT pk_dmquydoi PRIMARY KEY(maqd),CONSTRAINT fk_dmquydoi_dmsanpham FOREIGN KEY (masp) REFERENCES dmsanpham(masp))");
            exc.Exec_data("create table nhapth(id numeric(20, 0),sohoadon nvarchar(50),ngayhoadon datetime,tylechietkhau numeric(3, 0),mancc varchar(20),ngaynhap datetime,manv varchar(20),tongsoluong numeric(20, 0),tongtien numeric(19, 4),makho varchar(20),khuyenmai numeric(1, 0),sudung numeric(1, 0) default 1,userid numeric(5, 0) default 0,ngayud datetime default getdate(),CONSTRAINT pk_nhapth PRIMARY KEY(id),CONSTRAINT fk_nhapth_dmncc FOREIGN KEY (mancc) REFERENCES dmncc(mancc),CONSTRAINT fk_nhapth_dmnhanvien FOREIGN KEY (manv) REFERENCES dmnhanvien(manv),CONSTRAINT fk_nhapth_dmkho FOREIGN KEY (makho) REFERENCES dmkho(makho))");
            exc.Exec_data("create table nhapct(id numeric(20, 0),stt numeric(20, 0),masp varchar(20),soluong numeric(20, 2),dongia numeric(19, 4),vat numeric(3, 0),thanhtien numeric(19, 4),khuyenmai numeric(1, 0),userid numeric(5, 0) default 0,ngayud datetime default getdate(),CONSTRAINT pk_nhapct PRIMARY KEY(id, stt),CONSTRAINT fk_nhapct_nhapth FOREIGN KEY (id) REFERENCES nhapth(id),CONSTRAINT fk_nhapct_dmsanpham FOREIGN KEY (masp) REFERENCES dmsanpham(masp))");
        }
    }
}
